package guoanmaker.provider.business.service;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import guoanmaker.provider.business.model.PersonalCollection;
import guoanmaker.provider.business.model.PersonalUser;
import guoanmaker.provider.business.model.ProvideCity;
import guoanmaker.provider.business.model.ProvideCommodity;
import guoanmaker.provider.business.modelnobind.PersonalCollectionMove;
import guoanmaker.provider.business.modelnobind.PersonalCollectionTypeMove;
import guoanmaker.provider.business.modelnobind.PersonalCollectionUser;
import guoanmaker.provider.business.repository.PersonalCollectionRepository;
import guoanmaker.provider.business.repository.ProvideCityRepository;
import guoanmaker.provider.business.repository.ProvideCommodityRepository;

@Service
public class PersonalCollectionService implements PersonalCollectionServiceInterface {

	@Autowired
	private PersonalCollectionRepository personalCollectionRepository;
	
	@Autowired
	private ProvideCommodityRepository provideCommodityRepository;
	
	@Autowired
	private ProvideCityRepository provideCityRepository;
	
	@PersistenceContext
	private EntityManager manager;
	
	
	public PersonalCollectionMove insertPersonalCollection(String personalUserId,String  provideCommodityId,String cityCode) {
		PersonalCollectionMove per=new PersonalCollectionMove();
		List<PersonalCollection> list=null;
		ProvideCity provideCity=null;
		if(cityCode!=null&&!cityCode.equals("")){
			provideCity=provideCityRepository.findIdBycode(cityCode);
			list=personalCollectionRepository.findByPersonalUser_idIsAndProvideCommodity_idIs(personalUserId,provideCommodityId,provideCity.getId());
		}
		else{
			list=personalCollectionRepository.findByPersonalUser_idIsAndProvideCommodity_idIs(personalUserId,provideCommodityId);
		}
		PersonalCollection p=null;
		if(list==null||(list!=null&&list.size()==0)){
			String cityId=null;
			if(cityCode!=null&&!cityCode.equals("")){
				if(provideCity!=null){
					cityId=provideCity.getId();
				}
			}
			List lists=provideCommodityRepository.selectByStatusId(provideCommodityId, 2);
			if(lists!=null&&lists.size()>0){
				ProvideCommodity com=(ProvideCommodity) lists.get(0);
				if(com.getIsTerritoryRestriction().equals("1")){
					if(cityId==null){
						per.setMsg(-1);//收藏失败
						return per;
					}
				}
				PersonalCollection personalCollection=new PersonalCollection();
				PersonalUser personalUser=new PersonalUser();
				personalUser.setId(personalUserId);
				ProvideCommodity provideCommodity=new ProvideCommodity();
				provideCommodity.setId(provideCommodityId);
				personalCollection.setPersonalUser(personalUser);
				personalCollection.setProvideCommodity(provideCommodity);
				personalCollection.setCollectionTime(new Date());
				personalCollection.setCityId(cityId);
				p=personalCollectionRepository.save(personalCollection);
				per.setId(p.getId());
				per.setMsg(1);//收藏成功
				return per;
			}
			else{
				per.setId(null);
				per.setMsg(0);
				return per;//该商品已下架或者删除
			}
		}
		else{
			p=list.get(0);
			per.setId(p.getId());
			per.setMsg(2);//该商品已被收藏
			return per;
		}
	}

	@Override
	@Transactional
	public void deletePersonalCollection(String personalUserId,String  provideCommodityId) {
		String jpql = "delete from PersonalCollection p where p.personalUser.id=:personalUserId and p.provideCommodity.id=:provideCommodityId"; 
		Query query = manager.createQuery(jpql); 
		query.setParameter("personalUserId",personalUserId);
		query.setParameter("provideCommodityId", provideCommodityId);
		int reuslt=query.executeUpdate();
		System.out.println(reuslt);
	}

	@Override
	public Map<String, Object> selectPersonalCollectionPage(String userid, Integer page, Integer size) {
		Map<String,Object> map=new HashMap<String,Object>();
		/*String jpql="SELECT a.id AS collid,b.id AS comid,b.commodity_name,b.commodity_smallimage,b.synopsis,b.type,b.final_price,"
				+ "'0' AS citycode,'全国' as cityname,b.sales_volume FROM t_personal_collection a,t_provide_commodity b WHERE a.collection_conid = "
				+ "b.id AND b.is_territory_restriction = 2 AND b.commodity_state = 2 AND a.collection_userid = '"+userid+"'"
				+ " union ALL SELECT a.id AS collid,b.id AS comid,b.commodity_name,b.commodity_smallimage,b.synopsis,b.type,"
				+ "c.final_price,d.code as citycode,d.name as cityname,b.sales_volume FROM t_personal_collection a,t_provide_commodity b, "
				+ "(select x.final_price,x.commodity_id,x.city_id ,count(x.commodity_id) from t_provide_commodity_service_range x,t_personal_collection y where x.city_id =y.city_id group by "
				+ "x.commodity_id ) c,t_provide_city d WHERE a.collection_conid = b.id and b.id=c.commodity_id and c.city_id=d.id AND "
				+ "b.is_territory_restriction = 1 AND b.commodity_state = 2 AND a.collection_userid = '"+userid+"'";*/
		String jpql="(SELECT a.id AS collid,b.id AS comid,b.commodity_name,"
				+ "b.commodity_smallimage,b.synopsis,b.type,b.final_price,'0' AS citycode, '全国' AS cityname ,b.sales_volume FROM "
				+ "t_personal_collection a, t_provide_commodity b WHERE a.collection_conid = b.id AND a.collection_userid "
				+ "= '"+userid+"' AND b.is_territory_restriction = 2) "
				+ "UNION ALL"
				+ " (SELECT a.id AS collid,b.id AS comid,b.commodity_name,b.commodity_smallimage,"
				+ "b.synopsis,b.type,r.final_price,t. CODE,t. NAME,b.sales_volume FROM t_personal_collection a,t_provide_commodity b, "
				+ "t_provide_commodity_service_range r, t_provide_city t WHERE 	a.collection_conid = b.id AND a.city_id = "
				+ "r.city_id AND a.city_id = t.id AND b.id = r.commodity_id AND a.collection_userid = '"+userid+"' "
				+ "AND b.is_territory_restriction = 1 GROUP BY b.id,a.city_id)";
		Query query = manager.createNativeQuery(jpql);
		int count=query.getResultList().size();
		query.setMaxResults(size);
		query.setFirstResult(page*size);
		List list=query.getResultList();
		List<PersonalCollectionUser> lists=new ArrayList();
		for(Object oo : list){
			Object os[]=(Object[])oo;
			lists.add(new PersonalCollectionUser((String)os[0], (String)os[1],(String)os[2],(String)os[3], 
					(String)os[4],(String)os[5],Double.parseDouble(os[6].toString()),(String)os[7],(String)os[8],(Integer)os[9]));
		}
		map.put("list", lists);
		map.put("count", count);
		return map;
	}
	public List<PersonalCollectionUser> selectPersonalCollection(String userid) {
		String jpql="select new guoanmaker.provider.business.modelnobind.PersonalCollectionUser(pb.id, pc.id, pb.commodityName, pbu.businessName, "
				+"pb.finalPrice, pbu.companyCity,pb.synopsis,pb.commodity_smallimage,pb.salesVolume,pb.type) "
				+ "from PersonalCollection pc left join pc.provideCommodity pb left join pc.personalUser user left join pb.providerBusiness pbu  where user.id=:userid order by pc.collectionTime asc";
		Query query = manager.createQuery(jpql);
		query.setParameter("userid", userid);
		List<PersonalCollectionUser> list=query.getResultList();
		for (int i = 0; i < list.size(); i++) {
			PersonalCollectionUser p=list.get(i);
			String jpql1="select new guoanmaker.provider.business.model.ProvideCity(pc.name) "
					+ "from ProvideCity pc where pc.code=:code";
			Query query1 = manager.createQuery(jpql1);
			query1.setParameter("code", p.getCompanyCity());
			List<ProvideCity> list1=query1.getResultList();
			if(list!=null&&list1.size()>0){
				ProvideCity provideCity= list1.get(0);
				p.setCompanyCity(provideCity.getName());
			}
			list.remove(i);
			list.add(i, p);
		}
		return list;
	}

	@Override
	public Map<String, Object> selectPersonalCollectionPagePCType(String type, String userid, Integer page,
			Integer size) {
		Map<String,Object> map=new HashMap<String,Object>();
		String sql="";
		if(type.equals("all")){
			/*sql="SELECT getsidfor1or2 (b.service_type_id, 2) AS sid, a.id AS collid,b.id AS comid,b.commodity_name,b.commodity_smallimage,b.synopsis,b.type,b.final_price,"
					+ "'0' AS citycode,'全国' as cityname FROM t_personal_collection a,t_provide_commodity b WHERE a.collection_conid = "
					+ "b.id AND b.is_territory_restriction = 2 AND b.commodity_state = 2 AND a.collection_userid = '"+userid+"'"
					+ " union ALL SELECT getsidfor1or2 (b.service_type_id, 2) AS sid, a.id AS collid,b.id AS comid,b.commodity_name,b.commodity_smallimage,b.synopsis,b.type,"
					+ "c.final_price,d.code as citycode,d.name as cityname FROM t_personal_collection a,t_provide_commodity b, "
					+ "(select x.final_price,x.commodity_id,x.city_id ,count(x.commodity_id) from t_provide_commodity_service_range x,t_personal_collection y where x.city_id =y.city_id group by "
					+ "x.commodity_id ) c,t_provide_city d WHERE a.collection_conid = b.id and b.id=c.commodity_id and c.city_id=d.id AND "
					+ "b.is_territory_restriction = 1 AND b.commodity_state = 2 AND a.collection_userid = '"+userid+"'";*/
			
			sql="(SELECT getsidfor1or2 (b.service_type_id, 2) AS sid,a.id AS collid,b.id AS comid,b.commodity_name,"
					+ "b.commodity_smallimage,b.synopsis,b.type,b.final_price,'0' AS citycode, '全国' AS cityname FROM "
					+ "t_personal_collection a, t_provide_commodity b WHERE a.collection_conid = b.id AND a.collection_userid "
					+ "= '"+userid+"' and b.commodity_state=2 AND b.is_territory_restriction = 2) "
					+ "UNION ALL"
					+ " (SELECT getsidfor1or2(b.service_type_id, 2) AS sid,a.id AS collid,b.id AS comid,b.commodity_name,b.commodity_smallimage,"
					+ "b.synopsis,b.type,r.final_price,t. CODE,t. NAME FROM t_personal_collection a,t_provide_commodity b, "
					+ "t_provide_commodity_service_range r, t_provide_city t WHERE 	a.collection_conid = b.id AND a.city_id = "
					+ "r.city_id AND a.city_id = t.id AND b.id = r.commodity_id AND a.collection_userid = '"+userid+"' "
					+ "AND  b.commodity_state=2 and b.is_territory_restriction = 1 GROUP BY b.id,a.city_id)";
		}else{
			/*sql="SELECT getsidfor1or2 (b.service_type_id, 2) AS sid,a.id AS collid,b.id AS comid,b.commodity_name,b.commodity_smallimage,b.synopsis,b.type,b.final_price,"
					+ "'0' AS citycode,'全国' as cityname FROM t_personal_collection a,t_provide_commodity b WHERE a.collection_conid = "
					+ "b.id AND b.is_territory_restriction = 2 AND b.commodity_state = 2 AND a.collection_userid = '"+userid+"' and getsidfor1or2 (b.service_type_id, 2) ='"+type+"'"
					+ " union ALL SELECT getsidfor1or2 (b.service_type_id, 2) AS sid, a.id AS collid,b.id AS comid,b.commodity_name,b.commodity_smallimage,b.synopsis,b.type,"
					+ "c.final_price,d.code as citycode,d.name as cityname FROM t_personal_collection a,t_provide_commodity b, "
					+ "(select x.final_price,x.commodity_id,x.city_id ,count(x.commodity_id) from t_provide_commodity_service_range x,t_personal_collection y where x.city_id =y.city_id group by "
					+ "x.commodity_id ) c,t_provide_city d WHERE a.collection_conid = b.id and b.id=c.commodity_id and c.city_id=d.id AND "
					+ "b.is_territory_restriction = 1 AND b.commodity_state = 2 AND a.collection_userid = '"+userid+"' and getsidfor1or2 (b.service_type_id, 2) ='"+type+"'";*/
			sql="(SELECT getsidfor1or2 (b.service_type_id, 2) AS sid,a.id AS collid,b.id AS comid,b.commodity_name,b.commodity_smallimage,b.synopsis,b.type,"
					+ "b.final_price,'0' AS citycode, '全国' AS cityname FROM t_personal_collection a, t_provide_commodity b WHERE a.collection_conid = b.id AND "
					+ "a.collection_userid = '"+userid+"' AND b.is_territory_restriction = 2 and b.commodity_state=2 and getsidfor1or2 (b.service_type_id, 2) ='"+type+"') "
					+ "UNION ALL "
					+ "(SELECT getsidfor1or2 (b.service_type_id, 2) AS sid,a.id AS collid,b.id AS comid,b.commodity_name,b.commodity_smallimage,b.synopsis,b.type,"
					+ "r.final_price,t. CODE,t. NAME FROM t_personal_collection a,t_provide_commodity b, t_provide_commodity_service_range r, t_provide_city t WHERE 	"
					+ "a.collection_conid = b.id AND a.city_id = r.city_id AND a.city_id = t.id AND b.id = r.commodity_id AND a.collection_userid = '"+userid+"' "
					+ "AND b.is_territory_restriction = 1 and b.commodity_state=2 and getsidfor1or2 (b.service_type_id, 2) ='"+type+"' GROUP BY b.id,a.city_id)";
		}
		Query query = manager.createNativeQuery(sql);
		List lists=query.getResultList();
		int count=query.getResultList().size();
		query.setMaxResults(size);
		query.setFirstResult(page*size);
		List list=query.getResultList();
		ArrayList<PersonalCollectionUser> arr = new ArrayList<>();
		for (Object row : list
		        ) {
		    Object[] cells = (Object[]) row;
		    arr.add(new PersonalCollectionUser ((String)cells[0],(String)cells[1], (String)cells[2],(String)cells[3],(String)cells[4], 
					(String)cells[5],(String)cells[6],/*Double.parseDouble(cells[7].toString())*/((BigDecimal)cells[7]).doubleValue(),(String)cells[8],(String)cells[9]));
		}
		map.put("commlist", arr);
		map.put("count", count);
		String typeCountSql="SELECT getsidfor1or2 (service_type_id, 2) sid,getsnamefor1or2 (service_type_id,"
				+ " 2) sname,count(1) FROM t_personal_collection pc LEFT JOIN t_provide_commodity tc on pc.collection_conid="
				+ "tc.id  where tc.commodity_state=2 and pc.collection_userid='"+userid+"' GROUP BY sid,sname";
		Query createNativeQuery = manager.createNativeQuery(typeCountSql);
		List resultList = createNativeQuery.getResultList();
		
		ArrayList<PersonalCollectionTypeMove> arr1 = new ArrayList<>();
		for (Object row : resultList
		        ) {
		    Object[] cells = (Object[]) row;
		    arr1.add(new PersonalCollectionTypeMove ((String)cells[0],(String)cells[1],
		    		((BigInteger)cells[2])));
		}
		map.put("typeCountList", arr1);
		return map;
	}

	@Override
	public void deleteCollection(String id) {
		personalCollectionRepository.delete(id);
	}

	
}
